-- @author fangpeiyun
-- @date 2022.11.14
-- 数据治理-数据质量-基线检查报告

use hdp_lbg_supin_zplisting;

insert overwrite table app_zp_rock_dqc_sla_check_result_day partition (dt = '${#date(0,0,0):yyyyMMdd#}')
select
    r.task_config_id,
    a.check_table,
    a.owner_id,
    get_json_object(r.exec_result,'$.config_produce_time') AS config_produce_time,
    get_json_object(r.exec_result,'$.real_produce_time')   AS real_produce_time,
    case when
        get_json_object(r.exec_result,'$.real_produce_time') = ""
        or get_json_object(r.exec_result,'$.real_produce_time') > get_json_object(r.exec_result,'$.config_produce_time')
    then 'true' else 'false' end as is_break
from (
    select a.*
    from (
        select id, task_config_id, task_type, check_cmd, exec_result, check_status, action, update_time, create_time,
               ROW_NUMBER() OVER (PARTITION BY task_config_id ORDER BY create_time DESC) rno
        from hdp_lbg_supin_zplisting.dwd_zp_rock_dqc_sla_check_mid_result
        where dt = '${#date(0,0,0):yyyyMMdd#}'
    ) a
    where a.rno = 1
) r
left join (
    select id,check_table,owner_id
    from hdp_lbg_supin_zplisting.app_dqc_check_task_config
) a on r.task_config_id = a.id
where r.task_type = 'sla_check'